SSMインベントリデータのクエリで使えるAthenaテーブルを色々作成する

SSMインベントリデータのクエリで使えるAthenaテーブルを色々作成する

#AWS Systems Manager Inventory
#Amazon Athena
#AWS
川原征大

川原征大

facebook logohatena logotwitter logo
Clock Icon2023.01.16

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

Systems Manager(SSM)インベントリ はSSM管理下のインスタンスの棚卸しに役立つ機能です。 インスタンスからメタデータを収集して、それらの情報をコンソールから確認できます。

また、 リソースデータの同期 機能を使って、収集したメタデータ(インベントリデータ)を S3バケットへ送信できます。 S3バケットのインベントリデータに対して Amazon AthenaやQuickSight を使って分析ができます。

例えば以下の記事では、Organizations環境のSSMインベントリデータを 1つのS3バケットへ集約しています。

今回はS3バケットのインベントリデータをAmazon Athenaで分析するための下準備(兼 私の備忘録)として、 それぞれのデータタイプで使えるテーブルを作成してみます。

前提

Athenaの初期セットアップ周りの詳細は割愛します。 こちら の『ステップ 1: データベースを作成する』を参考ください。

今回は ssm_inventory データベースを作成して、その配下にテーブルを作成していきます。

img

CREATE DATABASE IF NOT EXISTS ssm_inventory

また、SSMインベントリで取得されるメタデータについては以下の公式情報を参考ください。

AWS:InstanceInformation

以下クエリを流してテーブルを作成します。

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_instanceinformation`(
  `agentstatus` string,
  `agenttype` string,
  `agentversion` string,
  `computername` string,
  `instanceid` string,
  `ipaddress` string,
  `platformtype` string,
  `platformname` string,
  `platformversion` string,
  `instancestatus` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string)
PARTITIONED BY (
  `accountid` string,
  `region` string,
  `resourcetype` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:InstanceInformation/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

以下サンプルクエリです。

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_instanceinformation
SELECT
  resourceid,
  instancestatus,
  accountid,
  region
FROM aws_instanceinformation limit 10

img

AWS:Application

以下クエリを流してテーブルを作成します。

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_application`(
  `applicationtype` string,
  `installedtime` string,
  `architecture` string,
  `version` string,
  `summary` string,
  `packageid` string,
  `publisher` string,
  `release` string,
  `url` string,
  `name` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string,
  `epoch` string)
PARTITIONED BY (
  `accountid` string,
  `region` string,
  `resourcetype` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:Application/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

以下サンプルクエリです。

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_application
SELECT
  resourceid,
  name,
  version,
  accountid,
  region
FROM aws_application limit 10

img

AWS:ComplianceItem

以下クエリを流してテーブルを作成します。

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_complianceitem`(
  `status` string,
  `installedtime` string,
  `executiontype` string,
  `patchseverity` string,
  `title` string,
  `severity` string,
  `executiontime` string,
  `compliancetype` string,
  `classification` string,
  `id` string,
  `documentversion` string,
  `patchstate` string,
  `patchbaselineid` string,
  `documentname` string,
  `patchgroup` string,
  `executionid` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string,
  `cveids` string)
PARTITIONED BY (
  `accountid` string,
  `region` string,
  `resourcetype` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:ComplianceItem/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

以下サンプルクエリです。

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_complianceitem
SELECT
  resourceid,
  title,
  documentname,
  status,
  severity,
  accountid,
  region
FROM aws_complianceitem limit 10

img

AWS:Network

以下クエリを流してテーブルを作成します。

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_network`(
  `macaddress` string,
  `ipv6` string,
  `ipv4` string,
  `name` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string,
  `gateway` string,
  `dnsserver` string,
  `dhcpserver` string,
  `subnetmask` string)
PARTITIONED BY (
  `accountid` string,
  `region` string,
  `resourcetype` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:Network/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

以下サンプルクエリです。

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_network
SELECT
  resourceid,
  macaddress,
  name,
  ipv4,
  ipv6,
  accountid,
  region
FROM aws_network limit 10

img

AWS:Tag

以下クエリを流してテーブルを作成します。

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_tag`(
  `key` string,
  `value` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string)
PARTITIONED BY (
  `accountid` string,
  `region` string,
  `resourcetype` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:Tag/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

以下サンプルクエリです。

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_tag
SELECT
  resourceid,
  key,
  value,
  accountid,
  region
FROM aws_tag limit 10

img

そのほか(兼 おわりに)

以上、いくつかよく使いそうなインベントリデータのAthenaテーブルを作成してみました。

これ以降でそのほかインベントリデータのテーブル作成およびプレビューのSQLをまとめて記載します。

参考になれば幸いです。

AWS:AWSComponent

▼ テーブル作成

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_awscomponent`(
  `applicationtype` string,
  `installedtime` string,
  `architecture` string,
  `version` string,
  `summary` string,
  `packageid` string,
  `publisher` string,
  `release` string,
  `url` string,
  `name` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string)
PARTITIONED BY (
  `accountid` string,
  `region` string,
  `resourcetype` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:AWSComponent/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

▼ プレビュー

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_awscomponent
SELECT * FROM aws_awscomponent limit 10

AWS:BillingInfo

▼ テーブル作成

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_billinginfo`(
  `billingproductid` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string)
PARTITIONED BY (
  `accountid` string,
  `region` string,
  `resourcetype` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:BillingInfo/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

▼ プレビュー

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_billinginfo
SELECT * FROM aws_billinginfo limit 10

AWS:ComplianceSummary

▼ テーブル作成

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_compliancesummary`(
  `compliancetype` string,
  `patchgroup` string,
  `patchbaselineid` string,
  `status` string,
  `overallseverity` string,
  `executiontime` string,
  `executionid` string,
  `executiontype` string,
  `compliantcriticalcount` string,
  `complianthighcount` string,
  `compliantmediumcount` string,
  `compliantlowcount` string,
  `compliantinformationalcount` string,
  `compliantunspecifiedcount` string,
  `noncompliantcriticalcount` string,
  `noncomplianthighcount` string,
  `noncompliantmediumcount` string,
  `noncompliantlowcount` string,
  `noncompliantinformationalcount` string,
  `noncompliantunspecifiedcount` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string)
PARTITIONED BY (
  `accountid` string,
  `region` string,
  `resourcetype` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:ComplianceSummary/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

▼ プレビュー

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_compliancesummary
SELECT * FROM aws_compliancesummary limit 10

AWS:InstanceDetailedInformation

▼ テーブル作成

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_instancedetailedinformation`(
  `cpus` string,
  `osservicepack` string,
  `cpuhyperthreadenabled` string,
  `cpuspeedmhz` string,
  `cpusockets` string,
  `cpucores` string,
  `cpumodel` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string,
  `kernelversion` string)
PARTITIONED BY (
  `accountid` string,
  `region` string,
  `resourcetype` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:InstanceDetailedInformation/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

▼ プレビュー

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_instancedetailedinformation
SELECT * FROM aws_instancedetailedinformation limit 10

AWS:PatchSummary

▼ テーブル作成

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_patchsummary`(
  `baselineid` string,
  `missingcount` string,
  `lastnorebootinstalloperationtime` string,
  `installedothercount` string,
  `operationendtime` string,
  `othernoncompliantcount` string,
  `criticalnoncompliantcount` string,
  `installedpendingrebootcount` string,
  `noncompliantseverity` string,
  `snapshotid` string,
  `securitynoncompliantcount` string,
  `notapplicablecount` string,
  `operationstarttime` string,
  `rebootoption` string,
  `failedcount` string,
  `operationtype` string,
  `installedcount` string,
  `executionid` string,
  `installedrejectedcount` string,
  `patchgroup` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string,
  `ownerinformation` string,
  `unreportednotapplicablecount` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:PatchSummary/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

▼ プレビュー

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_patchsummary
SELECT * FROM aws_patchsummary limit 10

AWS:ResourceGroup

▼ テーブル作成

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_resourcegroup`(
  `arn` string,
  `name` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:ResourceGroup/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

▼ プレビュー

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_resourcegroup
SELECT * FROM aws_resourcegroup limit 10

AWS:Service

▼ テーブル作成

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_service`(
  `status` string,
  `servicetype` string,
  `servicesdependedon` string,
  `displayname` string,
  `dependentservices` string,
  `starttype` string,
  `name` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string)
PARTITIONED BY (
  `accountid` string,
  `region` string,
  `resourcetype` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:Service/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

▼ プレビュー

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_service
SELECT * FROM aws_service limit 10

AWS:WindowsUpdate

▼ テーブル作成

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_windowsupdate`(
  `installedtime` string,
  `description` string,
  `hotfixid` string,
  `installedby` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string)
PARTITIONED BY (
  `accountid` string,
  `region` string,
  `resourcetype` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:WindowsUpdate/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

▼ プレビュー

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_windowsupdate
SELECT * FROM aws_windowsupdate limit 10

AWS:WindowsRole

▼ テーブル作成

-- ${S3バケット} および ${プレフィクス} を置き換えてください
CREATE EXTERNAL TABLE `aws_windowsrole`(
  `path` string,
  `featuretype` string,
  `dependson` string,
  `description` string,
  `parent` string,
  `installed` string,
  `displayname` string,
  `installedstate` string,
  `subfeatures` string,
  `name` string,
  `servercomponentdescriptor` string,
  `resourceid` string,
  `capturetime` string,
  `schemaversion` string)
PARTITIONED BY (
  `accountid` string,
  `region` string,
  `resourcetype` string)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://${S3バケット}/${プレフィクス}/AWS:WindowsRole/'
TBLPROPERTIES (
  'classification'='json',
  'compressionType'='none',
  'typeOfData'='file')

▼ プレビュー

-- 初回実行時 or パーティション更新時(主に新規アカウントID or 新規リージョン追加時) には事前に以下パーティション更新クエリを流して下さい
-- MSCK REPAIR TABLE aws_windowsrole
SELECT * FROM aws_windowsrole limit 10

参考

Share this article

facebook logohatena logotwitter logo

関連記事

1-Click と Quick Setup の Host Management を使用した SSM インベントリ収集のデフォルトパラメーター設定を確認してみた

1-Click と Quick Setup の Host Management を使用した SSM インベントリ収集のデフォルトパラメーター設定を確認してみた

User avatar
片方 裕人
2024.11.05
SSM Inventory を使って便利に EC2 棚卸し（ハマりどころを添えて） #midosuji_tech

SSM Inventory を使って便利に EC2 棚卸し（ハマりどころを添えて） #midosuji_tech

User avatar
川原征大
2024.06.12
SSMインベントリとAthenaを使ったインスタンス棚卸し例

SSMインベントリとAthenaを使ったインスタンス棚卸し例

User avatar
川原征大
2023.11.06
EC2インスタンスに特定のアプリケーションがインストールされていない場合、Security Hub経由で自動通知させる仕組みを作ってみた

EC2インスタンスに特定のアプリケーションがインストールされていない場合、Security Hub経由で自動通知させる仕組みを作ってみた

User avatar
平井裕二
2023.08.08
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.